	SELECT 
	YR, 
	MN, 
	SUM(CASE	WHEN B.PRODUCT='IPTV' 	AND	DIRECTION='IN' THEN QTY ELSE	0 END	) IPTV_IN,  
	SUM(CASE	WHEN B.PRODUCT='IPTV' 	AND	DIRECTION='OUT' THEN QTY ELSE	0 END	) IPTV_OUT,  
	IPTV_IN-IPTV_OUT IPTV_NET,  
	SUM(CASE	WHEN B.PRODUCT='HSIA' 	AND	DIRECTION='IN' THEN QTY ELSE	0 END	) HSIA_IN,  
	SUM(CASE	WHEN B.PRODUCT='HSIA' 	AND	DIRECTION='OUT' THEN QTY ELSE	0 END	) HSIA_OUT,  
	HSIA_IN-HSIA_OUT HSIA_NET,  
	SUM(CASE	WHEN B.PRODUCT='VOIP' 	AND	DIRECTION='IN' THEN QTY ELSE	0 END	) VOIP_IN, 
	SUM(CASE	WHEN B.PRODUCT='VOIP' 	AND	DIRECTION='OUT' THEN QTY ELSE	0 END	) VOIP_OUT,  
	VOIP_IN-VOIP_OUT VOIP_NET, 
	IPTV_NET + HSIA_NET + VOIP_NET TOT_NET,
	'REGION' ROLLUP_TYPE,  
	REGION,
	PRODUCT, 
	LOCATION ,
	DWELLING

	FROM	
	(  -- B
			SELECT	C.DIRECTION, C.START_DT, 
		CASE UPPER(C.DWELLING_TYPE_U)  WHEN 'APT' THEN 'MDU' WHEN 'CONDO' THEN 'MDU' ELSE 'SFU' END DWELLING,
		COALESCE(PRODUCT_NM, 'ALL') PRODUCT, 
		COALESCE(CASE C.NATIONAL_ID WHEN 'YES' THEN 'NATIONAL'  WHEN 'NO' THEN 'REGIONAL' END, 'BOTH') LOCATION , 
		COALESCE(C.REGION, 'ALL') REGION,
		YR,
		MN,
		MARKET,
		STATE_CD,
		AREA_MGR,
		ACCT_MGR,
		PORTFOLIO,
		TRAC_ID,
		PROPERTY_NAME_U,
		QTY		
		FROM			
		( ---C
			SELECT
			 REGION_DESC_U REGION
			,MARKET_AREA_U MARKET
			,STATE STATE_CD
			,AM_U  ACCT_MGR
			,AE_ASSIGNED_U AREA_MGR
			,PORTFOLIO_NAME_U PORTFOLIO
			,STATE||PROPERTY_ID_U TRAC_ID
			,EXTRACT (MONTH FROM POSTDATE_U) MN
			,EXTRACT (YEAR FROM POSTDATE_U) YR
			,CASE 
			 WHEN INDEX(PRODUCT_NM_U,'VOICE') GT 0 THEN 'VOIP' 
			 WHEN INDEX(PRODUCT_NM_U,'HSIA') GT 0 THEN 'HSIA' 
			 ELSE 'IPTV' 
			 END PRODUCT_NM
			,CASE PRODUCT_ACTION_CD_U
			 WHEN 'O' THEN 'OUT'
			 WHEN 'I' THEN 'IN'  
			 END DIRECTION
			,PROD_QTY_U QTY
			,NATIONAL_IND_U NATIONAL_ID
			,DWELLING_TYPE_U
			,PROPERTY_NAME_U
			,POSTDATE_U START_DT			
			FROM
			
			
			( -- X

	
				SELECT 
				 REGION_U
				,STATE
				--,WIRE_CENTER
				,PROPERTY_ID_U
				,NATIONAL_IND_U
				,DWELLING_TYPE_U
--				,ADDRESS_U
--				,UNIT  AS UNIT_U
--				,CITY_NM_U
				,REGION_DESC_U
				--,SEGMENT_U
				,PROPERTY_NAME_U
				,AM_U
				,AE_ASSIGNED_U
				,PORTFOLIO_NAME_U
				--,PR_STATUS_U
				--,C_STATUS_U
				,MARKET_AREA_U
				--,DA_U
				--,WCDA_U
				---EVENT_ID_U
				--,SONBR_U
--				,DISC_RSN_CD_U
--				,DISC_RSN_U   
				--,SALES_CODE_U
				--,DIST_CHANNEL_U
				--,BAN_U
				--,ORDER_TYPE_U
				--,SOURCE_CD_U
				,POSTDATE_U
				,PRODUCT_ID_U
				,PRODUCT_NM_U       
--				,INWARD_OUTWARD_CD_U         
				,PRODUCT_ACTION_CD_U  
				,PROD_QTY_U
				 FROM
				(  -- TOT
					SELECT  
				 	CASE  STATE 
					WHEN  'IL'  THEN 'A'
					WHEN  'IN'  THEN 'A'
					WHEN  'MI'  THEN 'A'
					WHEN  'WI'  THEN 'A'
					WHEN  'OH'  THEN 'A'
					WHEN  'CA'  THEN 'P'
					WHEN  'NV'  THEN 'P'
					WHEN  'CT'  THEN 'N'
					WHEN  'TX'  THEN 'S'
					WHEN  'KS'  THEN 'S'
					WHEN  'AR'  THEN 'S'
					WHEN  'OK'  THEN 'S'
					WHEN  'MO'  THEN 'S'
					ELSE 'U'
					END  AS  REGION_U
					,STATE     /*   ADDED 9/9/09 PER MIST REQUEST  126821   */
					--,WIRE_CENTER
					,BASE.PROPERTY_ID AS PROPERTY_ID_U
					,NATIONAL_IND_U
					,DWELLING_TYPE_U
--					,ADDRESS  AS ADDRESS_U
--					,UNIT
--					,CITY_NM AS CITY_NM_U
					,REGION AS REGION_DESC_U
					,PROPERTY_NAME  AS PROPERTY_NAME_U
					,AM  AS AM_U
					,AE_ASSIGNED  AS AE_ASSIGNED_U
					,PORTFOLIO_NAME  AS PORTFOLIO_NAME_U
					--,PR_STATUS  AS PR_STATUS_U
					--,C_STATUS  AS C_STATUS_U
					,MARKET_AREA  AS MARKET_AREA_U
					--,DISTRIBUTION_AREA AS DA_U
					--,TRIM(WIRE_CENTER)||TRIM(DISTRIBUTION_AREA)  AS WCDA_U
					--,BASE.EVENT_ID  AS EVENT_ID_U
					--,SONBR  AS SONBR_U
--					,DISC_RSN_CD_U
--					,DISC_RSN_U   
					--,BASE.SALES_CODE AS SALES_CODE_U
					--,COALESCE(DIST_CHANNEL,SALES_CHANNEL_CD,'UNKNOWN')  AS DIST_CHANNEL_U
					--,BAN AS BAN_U
				--	,ORDER_TYPE  AS ORDER_TYPE_U
--					,SOURCE_CD  AS SOURCE_CD_U
					,POSTDATE  AS POSTDATE_U
					,BASE.PRODUCT_ID   AS PRODUCT_ID_U
					,PRODUCT_NM           AS PRODUCT_NM_U       
--					,INWARD_OUTWARD_CD    AS INWARD_OUTWARD_CD_U         
					,PRODUCT_ACTION_CD    AS PRODUCT_ACTION_CD_U  
					,PROD_QTY AS PROD_QTY_U
					FROM
					( -- LEGS
						SELECT  
						 PROPERTY_ID 
--						,ADDRESS
--						,UNIT
						,REGION
						,PROPERTY_NAME
						,NATIONAL_IND  AS  NATIONAL_IND_U
						,DWELLING_TYPE  AS DWELLING_TYPE_U
						,AM
						,AE_ASSIGNED
						,PORTFOLIO_NAME
						--,PR_STATUS
						--,C_STATUS
						,MARKET_AREA
						--,DISTRIBUTION_AREA
						--,CHK.EVENT_ID
						--,SONBR
--						,DISC_RSN_CD_U
--						,DISC_RSN_U   
						--,SALES_CODE
--						,SALES_CHANNEL_CD
						--,BAN
						--,ORDER_TYPE
--						,SOURCE_CD
						,POSTDATE
						,PRODUCT_ID   
--						,INWARD_OUTWARD_CD             
						,PRODUCT_ACTION_CD             
						,PROD_QTY  
--						,CITY_NM  
						,STATE   
						FROM
						( -- CHK
							SELECT 
							 S.PROPERTY_ID 
--							,S.ADDRESS
--							,UNIT
							,SC.REGION
							,SC.PROPERTY_NAME
							,SC.NATIONAL_IND
							,SC.DWELLING_TYPE
							,SC.AM
							,SC.AE_ASSIGNED
							,SC.PORTFOLIO_NAME
							--,SC.PR_STATUS
							--,SC.C_STATUS
							,SC.MARKET_AREA
							--,' ' AS DISTRIBUTION_AREA
--							,E.RAAID
							,E.EVENT_ID
--							,E.SONBR
--							,DISC_RSN_CD_U
--							,DISC_RSN_U   
							--,SALES_CODE
							--,SALES_CHANNEL_CD
							--,BAN
							--,ORDER_TYPE
--							,SOURCE_CD
							,E.POSTDATE
--							,E.CONTACT_NM
--							,E.CITY_NM
							,E.STATE
--							,E.ZIP_CD
							FROM 
							SMARTMOVES.TXSM012_SMOVES_ADDRESSES  
							S
							,SMARTMOVES.TXSM101_SO_SCORE 	
							SC
							,(	-- E		  		
					  			SELECT 
--					  			 RAAID
								EVENT_ID
--								,SONBR
--								,DISC_RSN_CD_U
--								,DISC_RSN_U   
								--,SALES_CODE
								--,SALES_CHANNEL_CD
								--,BAN
								--,ORDER_TYPE
--								,SOURCE_CD
								,POSTDATE
--								,CONTACT_NM
--								,CITY_NM
								,ADDRESS
--								,UNIT
								,STATE
    							,ZIP_CD					
								FROM
								( -- BASE
									SELECT	
									 --REPOSITORY_AFFILIATE_ACCT_ID RAAID
									EVENT_ID
--									,SONBR
--									,DISC_RSN_CD_U
--									,DISC_RSN_U   
									--,SALES_CODE
									--,SALES_CHANNEL_CD
									--,BAN
									--,ORDER_TYPE
--									,SOURCE_CD
									,POSTDATE
									--,ADDRESS_ID
								--,CONTACT_NM
								--,CITY_NM
									--,PRIMARY_ADDRESS_TXT
									,SECONDARY_ADDRESS_TXT AS  UNIT
									,TRIM(C.SECONDARY_ADDRESS_TXT) (NAMED UNIT_ADD)     
							         ,CASE WHEN C.STREET_DIRECTION_PREFIX_TXT > ' '      
							             THEN TRIM(C.STREET_DIRECTION_PREFIX_TXT) ||' '||
							                  TRIM(C.STREET_NM)                          
							             ELSE C.STREET_NM                                
							             END (NAMED ADR1)                                
							         ,CASE WHEN C.ADDRESS_HOUSE_NBR > ' '                
							             THEN TRIM(C.ADDRESS_HOUSE_NBR) ||' '|| TRIM(ADR1)    
							             ELSE ADR1                                            
							             END (NAMED ADR2)                                     
							         ,CASE WHEN C.ADDRESS_SUFFIX_TYPE_CD > ' '                
							             THEN TRIM(ADR2) ||' '|| TRIM(C.ADDRESS_SUFFIX_TYPE_CD)
							             ELSE ADR2                                            
							             END (NAMED ADR3)                                     
							         ,CASE WHEN C.STREET_DIRECTION_SUFFIX_TXT > ' '           
							             THEN TRIM(ADR3) ||' '||                              
							                  TRIM(C.STREET_DIRECTION_SUFFIX_TXT)             
							             ELSE ADR3                                            
							             END (NAMED ADDRESS)      
									,C.ZIP_CD
									,C.TERRITORY_CD AS STATE
								    FROM  
								 	ENTERPRISE_RETAIL_VIEWS.VCCG559_ACCOUNT_CNTCT_GEO_ADDR 
									C  
									,
									( --S
										SELECT 
			  							 H205.REPOSITORY_AFFILIATE_ACCT_ID  RAAID  
								 		,H205.EVENT_ID
    									,H205.SERVICE_ORDER_NBR        		SONBR
--										,DISC_RSN_CD_U
--										,DISC_RSN_U     
										--,O.BAN
										,H205.EVENT_POSTED_DT    			POSTDATE
--										,H205.EVENT_COMPLETION_DT    		CMPLDATE 
										--,H205.EVENT_CLASS_CD				ORDER_TYPE
--										,H205.DATA_SOURCE_CD				SOURCE_CD
--										,SUBSTR(H205.ORIGINATING_SALES_CD,1,7) AS SALES_CODE     
										,CASE WHEN O.SALES_CHANNEL_CD   = 'WEB' THEN 'ONLINE' ELSE O.SALES_CHANNEL_CD END AS SALES_CHANNEL_CD            
										FROM
										 ENTERPRISE_RETAIL_VIEWS.VCCH205_SERVICE_ORDER_EVENT H205
										 ,( --O
										 		SELECT 
										 		 O.ORDER_ID	
												,O.SERVICE_ORDER_CD
												--,O.BAN
												,SALES_CHANNEL_CD
--												,DISC_RSN_CD_U     
												--,DISC_RSN_U
											  	FROM	  TELCO_UNREG_RETAIL_VIEWS.VCTV800_ORDER	
											  	O					
												LEFT OUTER JOIN 
												( --D
													SELECT 
													 A.ORDER_ID
													,A.ORDER_ACTION_TYPE_CD
--													,A.ORDER_ACTION_REASON_CD   DISC_RSN_CD_U
--													,C.ORDER_ACTION_REASON_DESC DISC_RSN_U
													FROM
													TELCO_UNREG_RETAIL_VIEWS.VCTV801_ORDER_ACTION  	 
													A 
/*													INNER JOIN TELCO_UNREG_RETAIL_VIEWS.VCTV804_ORDER_ACTION_RSN_DESC  
													C
													  
													ON A.ORDER_ACTION_REASON_CD = C.ORDER_ACTION_REASON_CD
													AND	A.ORDER_ACTION_TYPE_CD = C.ORDER_ACTION_TYPE_CD
													AND A.ORDER_ACTION_REVISION_TYPE_CD = C.ORDER_ACTION_REVISION_TYPE_CD
													
													WHERE	A.CURRENT_ORDER_ACTION_STATUS_CD = 'DO'
													AND A.ORDER_ACTION_TYPE_CD IN ('CH', 'CE') */
											)
											D
										   	ON O.ORDER_ID = D.ORDER_ID
										)
										O	---------------------------------------------------------------------------------------------------------------------------------------------------------------												
										WHERE   
										H205.EVENT_POSTED_DT > DATE-369 
										AND  H205.DATA_SOURCE_CD IN (150)
										AND O.ORDER_ID = SONBR
										AND RAAID > 0
										--GROUP BY 1,2,3,4,5,6,7,8,9,10,11,12   
									) 
									S
									WHERE C.REPOSITORY_AFFILIATE_ACCT_ID = S.RAAID
			                        AND C.ZIP_CD <> ' '
									AND C.ADDRESS_USAGE_CD = 'S' 
								) 
								BASE
								--GROUP BY 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17
							) 
							E
							WHERE 
						    S.ZIP = E.ZIP_CD
							AND S.PROPERTY_ID = SC.PROPERTY_ID
							AND (SC.PR_STATUS = 'ACTIVE' AND C_STATUS = 'ACTIVE' ) 
							AND TRIM(S.ADDRESS) = TRIM(E.ADDRESS)
						) 
						CHK
						LEFT OUTER JOIN 
						( --ACT
							SELECT 
							 D.EVENT_ID
							,D.PRODUCT_ID                    
							--,D.INWARD_OUTWARD_CD             
							,CASE WHEN INWARD_OUTWARD_CD = 1 THEN 'I' ELSE 'O' END AS PRODUCT_ACTION_CD             
							,SUM(D.PRODUCT_QTY)    PROD_QTY                              
							FROM 
							ENTERPRISE_RETAIL_VIEWS.VCCH207_SVC_ORD_PROD_EVENT  
							D
							WHERE  D.DATA_SOURCE_CD IN (150)
							AND D.INWARD_OUTWARD_CD  IN (1,4)
							AND D.PRODUCT_ID > 0
							GROUP BY 1,2,3
						) 
						ACT
						ON CHK.EVENT_ID = ACT.EVENT_ID
					)
					BASE
					LEFT OUTER JOIN	
					( --P
						SELECT 
						 PRODUCT_ID
						,PRODUCT_NM
						FROM
						ENTERPRISE_RETAIL_VIEWS.VCCR200_PRODUCT   
						WHERE PRODUCT_LAST_EFFECTIVE_DT > DATE
						AND PRODUCT_ID > 0
						--GROUP BY 1,2
					)  
					P
					ON BASE.PRODUCT_ID = P.PRODUCT_ID
				/*   	LEFT OUTER JOIN	
					( --S
						SELECT 
						 SALES_CODE
						,CHANNEL  AS DIST_CHANNEL
						FROM
						MIS.SALES_CHANNEL_LOOKUP
						--GROUP BY 1,2
					) 
					S
					ON BASE.SALES_CODE = S.SALES_CODE					
					LEFT OUTER JOIN
						ADDED 9/9/09 PER MIST REQUEST  126821   
					(	--W
						SELECT 
						PROPERTY_ID
						,WIRE_CENTER
						FROM SMARTMOVES.TXSM011_SMOVES_PROPERTIES    
						--GROUP BY 1,2
					) 
					W
					ON BASE.PROPERTY_ID = W.PROPERTY_ID						
					WHERE PRODUCT_ACTION_CD IN ('I','O')
					--GROUP BY 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34*/
				) 
				TOT					
				/*  ADDED PER  MIST  131152: Determine/Develop Process for CLLI-DA Tracking for ACC- Uverse    
				LEFT OUTER JOIN
				(SELECT WCDA
				,SEGMENT AS SEGMENT_U
				FROM SPECIAL_TABLES.DG8331_ABCD_SEGS
				--GROUP BY 1,2)
				 SEG
				ON TOT.WCDA_U = SEG.WCDA
				--GROUP BY 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35 */
	

				) 
				X	--ACC_BASE_DATA		
	)
		C
	) 
	B 
	--WHERE MN='11' AND YR='2010'
	GROUP	BY CUBE  (REGION, B.PRODUCT, LOCATION, DWELLING), YR, MN 
	ORDER BY REGION, B.PRODUCT, LOCATION,  DWELLING, YR, MN 
	;
	
